# coding=utf-8
from datetime import datetime
import pandas as pd
from flask.blueprints import Blueprint
from flask import render_template, request, flash, redirect, url_for
from pathlib import Path
from werkzeug.utils import secure_filename
from src.api.response.R import R
from src.db.dbhelper import query_sql_by_pandas
from src.db.flaskdb import db, del_model, update_and_not_commit, db_commit, add_models
from src.api.response.LayR import LayR
from src.db.flaskdb import add_model
from src.model.material_type import MaterialType
from src.model.material_type_quantity import MaterialTypeQuantity
from src.model.product_model_connect import ProductModelConnect
from src.model.product_model_td import ProductModelTd
from src.util.pathutil import get_upload_dir

xh_api = Blueprint('xh_api', __name__, url_prefix="/api/xh")


@xh_api.route('/xh_manager_page', methods=["GET"])
def material_manager_page():
    sql = "select val, name from sys_dic_type where is_del = 0 and dic_type = '型号级别' "
    level_df = query_sql_by_pandas(sql)
    level_dic = dict(zip(level_df['val'], level_df['name']))
    qd_sql = "select name from sys_dic_type where is_del = 0 and dic_type = '渠道' "
    jd_sql = "select name from sys_dic_type where is_del = 0 and dic_type = '基地' "
    qd_df = query_sql_by_pandas(qd_sql)
    qd_dic = dict(zip(qd_df['name'], qd_df['name']))
    jd_df = query_sql_by_pandas(jd_sql)
    jd_dic = dict(zip(jd_df['name'], jd_df['name']))

    return render_template("/xh/xh_manager_page.html", level_dic=level_dic, qd_dic=qd_dic, jd_dic=jd_dic)


@xh_api.route("/get_xh_list", methods=["POST"])
def get_xh_list():
    r = LayR()
    limit = request.form.get("limit")
    page = request.form.get("page")
    product_model = request.form.get("product_model")
    paginate = None
    if product_model is None or product_model == "":
        paginate = ProductModelTd.query.order_by(ProductModelTd.product_model.asc())
    else:
        paginate = (ProductModelTd.query.filter(ProductModelTd.product_model.like(f"%{product_model}%")).
                    order_by(ProductModelTd.product_model.asc()))
    pagination = paginate.paginate(page=int(page), per_page=int(limit))
    items = pagination.items
    total = pagination.total
    lis = []
    for item in items:
        lis.append(item.to_dict())
    r.data = lis
    r.count = total
    return r.to_dict()


@xh_api.route("/add_xh", methods=["POST"])
def add_xh():
    product_model = request.form.get("product_model")
    product_name = request.form.get("product_name")
    product_level = request.form.get("product_level")
    # qd = request.form.get("qd")
    # jd = request.form.get("jd")
    if product_model is None or product_model == "":
        return {
            "code": 500,
            "msg": "型号为空"
        }
    first = ProductModelTd.query.filter(ProductModelTd.product_model == product_model).filter(
        MaterialType.is_del == 0).first()
    # .filter(ProductModelTd.qd == qd).filter(ProductModelTd.jd == jd)
    if first is None:
        # 新增
        product = ProductModelTd()
        product.product_model = product_model
        product.product_name = product_name
        product.product_level = product_level
        # product.qd = qd
        # product.jd = jd
        product.is_del = 0
        add_model(product)
        # productC = ProductModelConnect()
        # productC.pro_id = product.id
        # productC.product_model = product_model
        # productC.relation = 0
        # productC.create_time = datetime.now()
        # add_model(productC)
        return {
            "code": 200,
            "msg": "新增成功"
        }
    else:
        return {
            "code": 500,
            "msg": "当前资产已经存在"
        }


@xh_api.route("/del", methods=["POST"])
def del_xh():
    product_id = request.form.get("id")
    first = ProductModelTd.query.filter(ProductModelTd.id == product_id).first()
    del_model(first)
    if first is not None:
        product_mo_id = first.id
        ProductModelConnect.query.filter(ProductModelConnect.pro_id == product_mo_id).delete()
        db.session.commit()
    return {
        "code": 200,
        "msg": "删除成功"
    }


@xh_api.route("/add_relation", methods=["POST"])
def add_relation():
    r = R()
    product_id = request.form.get("id")
    product_model = request.form.get("product_model")
    relation = request.form.get("relation")
    relations = str(relation).split(",")
    relations.remove("")
    relations_models = []
    c_date = datetime.now()
    # 获取旧的relation
    sql = "select relation from product_model_connect where is_del = 0 and pro_id = '{}'".format(product_id)
    old_relations_df = query_sql_by_pandas(sql)
    old_relations_ids = []
    other_relations_ids = []
    if old_relations_df is not None and old_relations_df.empty is False:
        old_relations_df["relation"] = old_relations_df["relation"].astype(str)
        old_relations_ids = old_relations_df["relation"].tolist()
    for relation_item in relations:
        if relation_item not in old_relations_ids:
            model = ProductModelConnect()
            model.product_model = product_model
            model.pro_id = product_id
            model.relation = int(relation_item)
            model.create_time = c_date
            model.is_del = 0
            model.scale = 1
            relations_models.append(model)
    for relation_item in old_relations_ids:
        if relation_item not in relations:
            other_relations_ids.append(relation_item)

    # 删除旧的
    old_relations = (ProductModelConnect.query.filter(ProductModelConnect.pro_id == product_id).
                     filter(ProductModelConnect.relation.in_(other_relations_ids)).delete())
    # db.session.commit()
    add_models(relations_models)
    r.code = 200
    r.msg = "添加成功"
    return r.to_dict()


@xh_api.route("/editor_relation", methods=["POST"])
def editor_relation():
    r = R()
    product_id = request.form.get("id")
    product_model = request.form.get("product_model")
    relation = request.form.get("key_val")
    print(product_model, relation)
    relations = str(relation).split("|")
    print(product_model, relations)
    relations.remove("")
    for item in relations:
        print(item, type(item))
        key_vals = item.split(",")
        if len(key_vals) < 2:
            continue
        relation_id = key_vals[0]
        scale = key_vals[1]
        print(relation_id, scale)
        model = (ProductModelConnect.query.filter(ProductModelConnect.relation == int(relation_id))
                 .filter(ProductModelConnect.product_model == product_model).first())
        if model is not None:
            model.scale = int(scale)
            db.session.commit()
    r.code = 200
    r.msg = "修改成功"
    return r.to_dict()


@xh_api.route("/get_relation", methods=["POST"])
def get_relation():
    product_id = request.form.get("id")
    relations = ProductModelConnect.query.filter(ProductModelConnect.pro_id == product_id).all()
    relations_models = []
    for relation in relations:
        relations_models.append(relation.relation)
    r = R()
    r.code = 200
    r.data = relations_models
    return r.to_dict()


@xh_api.route('/xh_relation_manager_page', methods=["GET"])
def xh_relation_manager_page():
    return render_template("/xh/xh_relation_manager_page.html")


@xh_api.route("/get_relation_xh_list", methods=["POST"])
def get_relation_xh_list():
    r = LayR()
    limit = request.form.get("limit")
    page = request.form.get("page")
    product_model = request.form.get("product_model")
    paginate = None
    if product_model is None or product_model == "":
        paginate = ProductModelTd.query
    else:
        paginate = ProductModelTd.query.filter(ProductModelTd.product_model.like(f"%{product_model}%"))
    pagination = paginate.paginate(page=int(page), per_page=int(limit))
    items = pagination.items
    total = pagination.total
    lis = []
    for item in items:
        lis.append(item.to_dict())
    r.data = lis
    r.count = total
    return r.to_dict()


@xh_api.route("/upload_xh", methods=["POST"])
def upload_xh():
    """上传文件"""
    r = R()
    if 'file' not in request.files:
        flash('No file part')
        return r.setResponse(code=500, msg="请先选择文件").to_dict()

    file = request.files['file']
    if file.filename == '':
        flash('No selected file')
        return r.setResponse(code=500, msg="请先选择文件").to_dict()

    if file:  # 这里可以加入文件类型判断等逻辑
        filename = secure_filename(file.filename)
        if filename.endswith("xlsx") or filename.endswith("xls"):
            file_path = Path.joinpath(get_upload_dir(), filename)
            file.save(file_path)
            # 读取文件
            df = pd.read_excel(file_path)
            print(df)
            file_path.unlink()
        else:
            return r.setResponse(code=500, msg="只允许上传以xlsx和xls结尾的Excel文件").to_dict()
        return r.setResponse(code=200, msg="上传成功").to_dict()